Public Class StoolExamResultHistory
    Protected _year As String
    Protected _police As New Police()
    Protected _color As New Color()
    Protected _consistency As New Consistency()
    Protected _wbc As String
    Protected _rbc As String
    Protected _occult_blood As New OccultBlood()
    Protected _stool_exam_status As Short
    Protected _police_fullname As String
    Protected _police_age As Integer
    Protected _Data As ArrayList
    Protected _DataCurrent As Integer

    Public Property year() As String
        Get
            Return _year
        End Get

        Set(ByVal value As String)
            _year = value
        End Set
    End Property

    Public Property police() As Police
        Get
            Return _police
        End Get
        Set(ByVal value As Police)
            _police = value
        End Set
    End Property

    Public Property color() As Color
        Get
            Return _color
        End Get
        Set(ByVal value As Color)
            _color = value
        End Set
    End Property

    Public Property consistency() As Consistency
        Get
            Return _consistency
        End Get
        Set(ByVal value As Consistency)
            _consistency = value
        End Set
    End Property

    Public Property wbc() As String
        Get
            Return _wbc
        End Get
        Set(ByVal value As String)
            _wbc = value
        End Set
    End Property

    Public Property rbc() As String
        Get
            Return _rbc
        End Get
        Set(ByVal value As String)
            _rbc = value
        End Set
    End Property

    Public Property occult_blood() As OccultBlood
        Get
            Return _occult_blood
        End Get
        Set(ByVal value As OccultBlood)
            _occult_blood = value
        End Set
    End Property

    Public Property stool_exam_status() As Short
        Get
            Return _stool_exam_status
        End Get
        Set(ByVal value As Short)
            _stool_exam_status = value
        End Set
    End Property

    Public Property police_fullname() As String
        Get
            Return _police_fullname
        End Get
        Set(ByVal value As String)
            _police_fullname = value
        End Set
    End Property

    Public Property police_age() As Integer
        Get
            Return _police_age
        End Get
        Set(ByVal value As Integer)
            _police_age = value
        End Set
    End Property

    Public Function getPoliceStoolExamInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceStoolExamResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceStoolExam")

        Return objDs
    End Function

    Protected Function getsPoliceStoolExamInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceStoolExamResultHistory"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceStoolExam")

        For Each objDr As System.Data.DataRow In objDs.Tables("PoliceStoolExam").Rows
            Dim obj As StoolExamResultHistory = New StoolExamResultHistory
            obj.police.citizen_id = Utilities.ClearDBNullToMinValue(objDr.Item("citizen_id"), TypeCode.String)
            obj.police_fullname = Utilities.ClearDBNullToMinValue(objDr.Item("full_name"), TypeCode.String)
            obj.police_age = Utilities.ClearDBNullToMinValue(objDr.Item("age"), TypeCode.Int32)
            obj.stool_exam_status = Utilities.ClearDBNullToMinValue(objDr.Item("stool_exam_status"), TypeCode.Int16)

            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function getPoliceStoolExam(ByVal inYear As String, ByVal inCitizenID As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceStoolExamResult"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceStoolExam")

        Return objDs
    End Function

    Protected Function getsPoliceStoolExam(ByVal inYear As String, ByVal inCitizenID As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceStoolExamResult"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", inCitizenID)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceStoolExam")

        For Each objDr As System.Data.DataRow In objDs.Tables("PoliceStoolExam").Rows
            Dim obj As StoolExamResultHistory = New StoolExamResultHistory
            obj.year = Utilities.ClearDBNullToMinValue(objDr.Item("year"), TypeCode.String)
            obj.police.citizen_id = Utilities.ClearDBNullToMinValue(objDr.Item("citizen_id"), TypeCode.String)
            obj.police_fullname = Utilities.ClearDBNullToMinValue(objDr.Item("full_name"), TypeCode.String)
            obj.police_age = Utilities.ClearDBNullToMinValue(objDr.Item("age"), TypeCode.Int32)
            obj.police.organization.organization_id = Utilities.ClearDBNullToMinValue(objDr.Item("organization_id"), TypeCode.String)
            obj.police.organization.organization_name_tha = Utilities.ClearDBNullToMinValue(objDr.Item("organization_id"), _
                TypeCode.String)
            obj.color.color_id = Utilities.ClearDBNullToMinValue(objDr.Item("color_id"), TypeCode.Int32)
            obj.consistency.consistency_id = Utilities.ClearDBNullToMinValue(objDr.Item("consistency_id"), TypeCode.Int32)
            obj.wbc = Utilities.ClearDBNullToMinValue(objDr.Item("wbc"), TypeCode.String)
            obj.rbc = Utilities.ClearDBNullToMinValue(objDr.Item("rbc"), TypeCode.String)
            obj.occult_blood.occult_blood_id = Utilities.ClearDBNullToMinValue(objDr.Item("occult_blood_id"), TypeCode.Int32)
            obj.stool_exam_status = Utilities.ClearDBNullToMinValue(objDr.Item("stool_exam_status"), TypeCode.Int16)
            obj.police.create_user = Utilities.ClearDBNullToMinValue(objDr.Item("create_user"), TypeCode.String)
            obj.police.create_date = Utilities.ClearDBNullToMinValue(objDr.Item("create_date"), TypeCode.DateTime)
            obj.police.update_user = Utilities.ClearDBNullToMinValue(objDr.Item("update_user"), TypeCode.String)
            obj.police.update_date = Utilities.ClearDBNullToMinValue(objDr.Item("update_date"), TypeCode.DateTime)

            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Sub LoadPoliceStoolExamInOrganizationAll(ByVal inOrganizationID As String, ByVal inYear As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsPoliceStoolExamInOrganizationAll(inOrganizationID, inYear)
    End Sub

    Public Sub LoadPoliceStoolExam(ByVal inYear As String, ByVal inCitizenID As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsPoliceStoolExam(inYear, inCitizenID)
    End Sub

    Public Function NextRecord() As Boolean
        If ((Not _Data Is Nothing) AndAlso (((Not _Data Is Nothing) AndAlso _
            (_DataCurrent < _Data.Count And _Data.Count > 0)))) Then
            _year = _Data(_DataCurrent).year
            _police.citizen_id = _Data(_DataCurrent).police.citizen_id
            _police_fullname = _Data(_DataCurrent).police_fullname
            _police_age = _Data(_DataCurrent).police_age
            _police.organization.organization_id = _Data(_DataCurrent).police.organization.organization_id
            _police.organization.organization_name_tha = _Data(_DataCurrent).police.organization.organization_name_tha
            _color.color_id = _Data(_DataCurrent).color.color_id
            _consistency.consistency_id = _Data(_DataCurrent).consistency.consistency_id
            _wbc = _Data(_DataCurrent).wbc
            _rbc = _Data(_DataCurrent).rbc
            _occult_blood.occult_blood_id = _Data(_DataCurrent).occult_blood.occult_blood_id
            _stool_exam_status = _Data(_DataCurrent).stool_exam_status
            _police.create_user = _Data(_DataCurrent).police.create_user
            _police.create_date = _Data(_DataCurrent).police.create_date
            _police.update_user = _Data(_DataCurrent).police.update_user
            _police.update_date = _Data(_DataCurrent).police.update_date
            _DataCurrent += 1
            Return True
        End If
        Return False
    End Function

    Public Function insertPoliceStoolExamResultHistory(ByVal inPoliceStoolExamResultHistory As StoolExamResultHistory) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_PoliceStoolExamResultHistory"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inPoliceStoolExamResultHistory.police.citizen_id)))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.organization.organization_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationNameTha", _
                 Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.organization.organization_name_tha))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.year))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inColorID", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.color.color_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inConsistencyID", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.consistency.consistency_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inWbc", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.wbc))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inRbc", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.rbc))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOccultBloodID", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.occult_blood.occult_blood_id))
            objCmd.Parameters.Add(objParam)


            objParam = New MySql.Data.MySqlClient.MySqlParameter("inStoolExamStatus", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.stool_exam_status))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inCreateUser", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.create_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateUser", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.update_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateDate", _
                Utilities.ConvertDateTimeFormat(inPoliceStoolExamResultHistory.police.update_date.ToString()))
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function deleteStoolExamResultHistory(ByVal inPoliceStoolExamResultHistory As StoolExamResultHistory) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_DELETE_PoliceStoolResult"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inPoliceStoolExamResultHistory.police.citizen_id)))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.organization.organization_id))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationNameTha", _
                 Utilities.SendToDatabase(inPoliceStoolExamResultHistory.police.organization.organization_name_tha))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inYear", _
                Utilities.SendToDatabase(inPoliceStoolExamResultHistory.year))
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function insertPoliceStoolExamResultHistory(ByVal inYear As String, ByVal inOrganizationID As String, _
    ByVal inCreateUser As String) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_PoliceStoolExamResultHistoryAll"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inCreateUser", inCreateUser)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Sub insertStoolExamSummaryReportValue(ByVal inYear As String, ByVal inOrganizationID As String)
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_StoolExamSummaryReport"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub insertStoolExamAbnormalSummaryReportValue(ByVal inYear As String, ByVal inOrganizationID As String)
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_StoolAbnormalSummaryReport"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", inYear)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub


End Class
